﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using MySql.Data.MySqlClient;


namespace data_upload_HCollage
{
    public partial class UpdateStudentForm : Form
    {
        public UpdateStudentForm()
        {
            InitializeComponent();
        }

        private void btnBrowse_Click(object sender, EventArgs e)
        {
            if (upStd_openFileDialog.ShowDialog() == DialogResult.OK)
            {
                txtFileLocation.Text = upStd_openFileDialog.FileName;
            }
        }

        private void btnUpdateStd_Click(object sender, EventArgs e)
        {
            string student_id = "";
            string academic_session = "";
            string group_id = cmbGroup_id.Text;
            string student_name = "";
            string fathername = "";
            string mothername = "";
            string ssc_board = "";
            string ssc_year = "";
            string ssc_group = "";
            string ssc_reg_no = "";
            string ssc_reg_year = "";
            string ssc_centre = "";
            string ssc_roll = "";
            string ssc_gpa = "";
            string ssc_opt = "";
            string ssc_school = "";
            string presentaddress = "";
            string permanentadd = "";
            string dob = "";
            string religion = "";
            string sex = "";
            string dateofadmission = "";
            string mother_lang = "";
            string phone_reg = "";
            string phone_off = "";
            string hsc_board = "";
            string hsc_year = "";
            string hsc_group = "";
            string hsc_reg_no = "";
            string hsc_reg_year = "";
            string hsc_centre = "";
            string hsc_roll = "";
            string hsc_gpa = "";
            string hsc_opt = "";
            string hsc_medium = "";    

            try
            {
                // OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtFileLocation.Text + ";Extended Properties=Excel 8.0");
                OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtFileLocation.Text + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";");
                OleDbDataAdapter da = new OleDbDataAdapter("select * from student", conn);
                DataTable dt = new DataTable();
                da.Fill(dt);
                int i = 0;
                MySqlConnection connMysql = new MySqlConnection(dbConnection.ConStr);
                connMysql.Open();
               
                foreach (DataRow row in dt.Rows)
                {
                    student_id = row["roll"].ToString();
                    academic_session = row["academic_session"].ToString().Replace(" ","");
                    student_name = row["student_name"].ToString().ToString().Replace("'", @"\'"); ;

                    fathername = row["fathername"].ToString().ToString().Replace("'", @"\'"); ;
                    mothername = row["mothername"].ToString().ToString().Replace("'", @"\'"); ;

                    presentaddress = row["presentaddress"].ToString().Replace("'", @"\'"); ;
                    permanentadd = row["permanentadd"].ToString().Replace("'", @"\'"); ;
                    dob = row["dob"].ToString();
                    religion = row["religion"].ToString();
                    sex = row["sex"].ToString();
                    dateofadmission = row["dateofadmission"].ToString();
                    mother_lang = row["mother_lang"].ToString();
                    phone_reg = row["phone_reg"].ToString();
                    phone_off = row["phone_off"].ToString();


                    ssc_board = row["ssc_board"].ToString();
                    ssc_year = row["ssc_year"].ToString();
                    ssc_group = row["ssc_group"].ToString();
                    ssc_reg_no = row["ssc_reg_no"].ToString();
                    ssc_reg_year = row["ssc_reg_year"].ToString();
                    ssc_centre = row["ssc_centre"].ToString().ToString().Replace("'", @"\'"); ;
                    ssc_roll = row["ssc_roll"].ToString();
                    ssc_gpa = row["ssc_gpa"].ToString();
                    ssc_opt = row["ssc_opt"].ToString();
                    ssc_school = row["ssc_school"].ToString().Replace("'", @"\'");

                    hsc_board = row["hsc_board"].ToString();
                    hsc_year = row["hsc_year"].ToString();
                    hsc_group = row["hsc_group"].ToString();
                    hsc_reg_no = row["hsc_reg_no"].ToString();
                    hsc_reg_year = row["hsc_reg_year"].ToString();
                    hsc_centre = row["hsc_centre"].ToString().ToString().Replace("'", @"\'"); ;
                    hsc_roll = row["hsc_roll"].ToString();
                    hsc_gpa = row["hsc_gpa"].ToString();
                    hsc_opt = row["hsc_opt"].ToString();
                    hsc_medium = row["hsc_medium"].ToString();
                    
                    String update_general_info_sql = " update student set " +
                                        " student_name='"+student_name+"',"+
                                        " student_name='" + student_name + "'," +
                                       // " section='" + +"'," +
                                        "fathername ='" + fathername + "'," +
                                        " mothername='" + mothername + "'," +
                                        " presentaddress='" + presentaddress + "'," +
                                        " permanentadd='" + permanentadd + "'," +
                                        " dob=str_to_date('" + dob + "','%d-%m-%Y')," +
                                        " religion='" + religion + "'," +
                                        " sex='" + sex + "'," +
                                        " dateofadmission=str_to_date('" + dateofadmission + "','%d-%m-%Y')," +
                                        " mother_lang='" + mother_lang + "'," +
                                        " phone_reg='" + phone_reg + "'," +
                                        " phone_off='" + phone_off + "'" +

                                        " where student_id='" + student_id + "'" +
                                        " and academic_session='" + academic_session + "'" ;


                    MySqlCommand Cmd_update = new MySqlCommand(update_general_info_sql, connMysql);
                    Cmd_update.ExecuteNonQuery();

                    String update_edu_sql =" update education set " +
                                        " ssc_board='" + ssc_board + "'," +
                                        " ssc_year='" + ssc_year + "'," +
                                        " ssc_group ='" + ssc_group + "'," +
                                        " ssc_reg_no='" + ssc_reg_no + "'," +
                                        " ssc_reg_year='" + ssc_reg_year + "'," +
                                        " ssc_centre='" + ssc_centre + "'," +
                                        " ssc_roll='" + ssc_roll + "'," +
                                        " ssc_gpa='" + ssc_gpa + "'," +
                                        " ssc_opt='" + ssc_opt + "'," +
                                        " ssc_school='" + ssc_school + "'," +
                                        " hsc_board='" + hsc_board + "'," +
                                        " hsc_year='" + hsc_year + "'," +
                                        " hsc_group='" + hsc_group + "'," +
                                        " hsc_reg_no ='" + hsc_reg_no + "'," +
                                        " hsc_reg_year='" + hsc_reg_year + "'," +
                                        " hsc_centre='" + hsc_centre + "'," +
                                        " hsc_roll='" + hsc_roll + "'," +
                                        " hsc_gpa='" + hsc_gpa + "'," +
                                        " hsc_opt='" + hsc_opt + "'," +
                                        " hsc_medium='" + hsc_medium + "'" +

                                        " where student_id='" + student_id + "'" +
                                        " and academic_session='" + academic_session + "'" ;

                    MySqlCommand Cmd_edu_update = new MySqlCommand(update_edu_sql, connMysql);
                    Cmd_edu_update.ExecuteNonQuery();               

                    i++;
                }
                connMysql.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            MessageBox.Show("Done!!!!!!!!");
        }

        private void UpdateStudentForm_Load(object sender, EventArgs e)
        {
            cmbGroup_id.Text = "Please Select Group";
        }
    }
}
